CASE: 表一 新增字段 AGE, 需要从 表二中 将AGE数据同步到表一中,  或者 单纯同步两张表中的数据

表1:EMPLOYEE(被更新的表  字段  age  )

表2: T_USER  数据来源表 

只更新表1中 age 为null的数据

UPDATE EMPLOYEE E SET AGE = (SELECT U.AGE FROM T_USER U WHERE E.ID=U.ID )   WHERE EXISTS (SELECT 1 FROM T_USER U WHERE E.ID=U.ID AND E.AGE IS NULL  )

更新表1全部数据

UPDATE EMPLOYEE E SET AGE = (SELECT U.AGE FROM T_USER U WHERE E.ID=U.ID )   WHERE EXISTS (SELECT 1 FROM T_USER U WHERE E.ID=U.ID)

UPDATE EMPLOYEE E SET AGE = (SELECT U.AGE FROM T_USER U WHERE E.ID=U.ID )   WHERE  E.ID IN (SELECT ID FROM T_USER) AND E.AGE IS NULL

更新一个字段:

MERGE INTO EMPLOYEE E USING T_USER U ON (E.ID=U.ID ) WHEN MATCHED THEN UPDATE SET E.AGE=U.AGE WHERE E.AGE IS NULL

更新多个字段: (与方法一和方法二相比,当更新多个字段时这种方法很方便)

MERGE INTO EMPLOYEE E USING T_USER U ON (E.ID=U.ID ) WHEN MATCHED THEN UPDATE SET E.AGE=U.AGE,E.NAME=U.NAME

BEGIN

FOR CUR IN (

SELECT U.ID,U.NAME,U.AGE FROM T_USER U,EMPLOYEE E WHERE U.ID=E.ID

)LOOP

UPDATE EMPLOYEE E SET E.NAME= CUR.NAME,E.AGE=CUR.AGE WHERE E.ID=CUR.ID;

END LOOP;

END;